#ifndef SQLMANAGEMENT_H
#define SQLMANAGEMENT_H

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <QString>
#include <QDebug>
#include <QVariant>
#include <QSqlQuery>
#include <QObject>

#include "Logger.hpp"

class SQLManagement : public QObject
{
    Q_OBJECT
public:
    SQLManagement();
    SQLManagement(const QString &host, const QString &user, const QString &password, const QString &database);

    ~SQLManagement() {
        // close connection
        disconnect();
    }

    bool connect() {
        try {
            // connect to mariadb
            driver = get_driver_instance();
            con = driver->connect(host.toStdString(), user.toStdString(), password.toStdString());
            con->setSchema(database.toStdString());
            LOG_INFO() << "[mysql connect success!]";
            return true;
        } catch (sql::SQLException &e) {
            LOG_ERROR() << "[mysql connect failed!]";
        }
    }

    void disconnect(){
        if(con) {
            delete con;
            con = nullptr;
            LOG_INFO() << "mysql connection closed.";
        }
    }

    bool executeQuery(const QString &query) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));
            pstmt->execute();
            LOG_INFO() << "query success";
            return true;
        } catch (sql::SQLException &e) {
            LOG_ERROR() << "query failed" << e.what();
            return false;
        }
    }

    QVariant executeQuerySingleResult(const QString &query) {
        try {
            // 准备 SQL 查询
            std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));

            // 执行查询
            std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

            // 检查是否有结果
            if (resultSet->next()) {
                // 返回第一列的值
                return QString::fromStdString(resultSet->getString(1)); // 1-based index
            }
        } catch (sql::SQLException &e) {
            LOG_ERROR() << "query failed" << e.what();
        }

        return QVariant(); // 没有结果时返回无效的 QVariant
    }

    QVariantMap executeQueryResult(const QString &query) {
        QVariantMap resultMap; // 用于存储查询结果

        try {
            // 准备 SQL 查询
            std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));

            // 执行查询并获取结果集
            std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

            // 检查是否有结果
            if (resultSet->next()) {
                // 获取各列的值并存入 QVariantMap
                resultMap["username"] = QString::fromStdString(resultSet->getString("username"));
                resultMap["age"] = QString::fromStdString(resultSet->getString("age"));
                resultMap["gender"] = QString::fromStdString(resultSet->getString("gender"));
                resultMap["phone"] = QString::fromStdString(resultSet->getString("phone"));
                resultMap["address"] = QString::fromStdString(resultSet->getString("address"));
            }
        } catch (sql::SQLException &e) {
            LOG_ERROR() << "Query failed: " << e.what();
        }

        return resultMap; // 返回查询结果或空的 QVariantMap
    }

private:
    QString host;
    QString database;
    QString user;
    QString password;
    // sql::mysql::MySQL_Driver *driver;
    sql::Driver* driver;
    sql::Connection *con;
};

#endif // SQLMANAGEMENT_H
